-- STANDARD SCRIPT
-- INSERT DATA IN insurance1_t
--
--


------------------------------------------------
-- FUNCTION TO INSERT NEW ROWS INTO ACCOUNT TABLE


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION upd_insurance1_f() RETURNS integer AS $$
DECLARE
  v_status integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_insurance1_t limit 1;
SELECT INTO v_cust_id cust_id from batch_insurance1_t limit 1;

PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,7,'START INSERT IN insurance1_t');



-- STEP 1 INSERT RECORDS INTO insurance1_t    

RAISE NOTICE 'START INSERT INTO insurance1_t TABLE';
  
  INSERT INTO insurance1_t
  SELECT
    a.cust_id               ,
    a.treatment_code        ,
    a.our_run_date          ,
    a.seqnum                ,
    a.cpi                   ,
    a.acctnum               ,
    a.acctnum_seq           ,
    a.hid                   ,
    a.individual_matchkey   ,
    a.client_npi1           ,
    a.client_npi2           ,
    a.name_type             ,
    a.lname                 ,
    a.mname                 ,
    a.fname                 ,
    a.addr1                 ,
    a.addr2                 ,
    a.city                  ,
    a.state                 ,
    a.zip5                  ,
    a.zip4                  ,
    a.ssn                   ,
    a.gender                ,
    a.dob                   ,
    a.g_lname               ,
    a.g_mname               ,
    a.g_fname               ,
    a.g_addr1               ,
    a.g_addr2               ,
    a.g_city                ,
    a.g_state               ,
    a.g_zip5                ,
    a.g_zip4                ,
    a.g_ssn                 ,
    a.g_gender              ,
    a.g_dob                 ,
    a.relationship_flag     ,
    a.g_employ_name         ,
    a.provider_state        ,
    a.service_date_begin    ,
    a.service_date_end      ,
    a.pat_proc_type         ,
    a.pat_type              ,
    a.cpt                   ,
    a.drg                   ,
    a.physician_code        ,
    a.charges               ,
    a.balance               ,
    a.acct_status           ,
    a.obs_ind               ,
    a.medicaid_max_date     ,
    a.medicare_max_date     ,
    a.third_party_sent_date ,
    a.hippa_flag_com        ,
    a.hippa_flag_gov        ,
    a.processing_ssn_flag   ,
    a.emdeon_sent_date      ,
    a.emdeon_tpl_flag       ,
    a.emdeon_commercial_flag,
    a.emdeon_medicare_flag  ,
    a.emdeon_medicaid_flag  ,
    a.emdeon_medicare_fast  ,
    a.output_id             ,
    a.current_commercial_run ,
    a.current_tpl_run        ,
    a.current_medicaid_run   ,
    a.current_medicare_run   ,
    a.commercial_score       ,
    a.scoring_code_commercial,
    a.tpl_score              ,
    a.scoring_code_tpl       ,
    a.medicaid_score         ,
    a.scoring_code_medicaid  ,
    a.medicare_score         ,
    a.scoring_code_medicare  ,
    a.client_field1          ,
    a.client_field2          ,
    a.client_field3          ,
    a.client_date1         

  FROM
    (SELECT * FROM batch_insurance1_t 
     WHERE 
       ( 
        (current_commercial_run <> prior_commercial_run)
        OR
        (current_tpl_run <> prior_tpl_run)
        OR
        (current_medicaid_run <> prior_medicaid_run)
        OR
        (current_medicare_run <> prior_medicare_run)
       ) 
  
       AND

       ( current_commercial_run = 'Y' OR current_tpl_run = 'Y' OR current_medicaid_run = 'Y' OR current_medicare_run = 'Y' )

    ) as a
  ; 

GET DIAGNOSTICS v_status = ROW_COUNT;
RAISE NOTICE 'DONE INSERT INTO insurance1_t TABLE : %', v_status;




-- STEP 2 UPDATE batch_status_insurance1_t WITH THE NUMBER INSERTED 

  UPDATE batch_status_insurance1_t
    SET
      insurance1_inserted = v_status
    WHERE
      (batch_status_insurance1_t.last_run_date = v_batch_run_date and batch_status_insurance1_t.cust_id = v_cust_id);

PERFORM ml_upd_batch_status_insurance1_new_f(v_cust_id,v_batch_run_date,1,7,'FINISH INSERT IN insurance1_t');



  return v_status;
END;
$$ LANGUAGE plpgsql;
